﻿if exists (select name from sysobjects where name = 'vCumulativeTimeAvg' and type = 'V')
drop View vCumulativeTimeAvg
GO

USE [NPMonitor]
GO

CREATE VIEW [vCumulativeTimeAvg]
AS
	SELECT 
		HourDate AS BeginTime, ScenarioNo, mp.PoolId As LocationId,
		(CASE WHEN Count(ActionId) = 0 THEN NULL ELSE (SUM(ISNULL([Time], 0)) / Count(ActionId)) END) AS AverageTime 
	FROM
		(
			SELECT     
				ActionId, Started, BeginTime, BeginTimeFormat, Time, 
				Url, Title, SendBytes, ReceivedBytes, ErrorCount, 
				PoolId, MachineName, HistoryUniqueId, ScenarioNO,
				CAST((CONVERT(VARCHAR(13), BeginTimeFormat, 120) + ':00:00') AS DateTime) AS HourDate
				--CONVERT(nvarchar(10), BeginTimeFormat,101) + ' ' + CONVERT(NVARCHAR(2),BeginTimeFormat,108) + ':00' AS HourDate
			FROM
				HttpActions
			WHERE
				BeginTimeFormat Between DATEADD(d, -1, GETDATE()) AND GETDATE()
				AND ScenarioNo IS NOT NULL AND LTRIM(RTRIM(ScenarioNo)) <> ''
				AND [Time] IS NOT NULL
		) ha
	LEFT JOIN 
		MachinePool mp ON ha.PoolId = mp.PoolId
		GROUP BY HourDate, ScenarioNO, mp.PoolId, mp.PoolName



GO